* Set current directory and open log
cd "E:\Seagate Sync\VOL\Personal folder\Documents\Research\Current projects\Working party meetings\Posted\EUP 160404"
capture log close
log using "Data management\wpar-management07-wplife-prepare", replace text


**************************************************************************
* Preparation of working party life-cycle data for merge with meeting data
**************************************************************************


* Project and author information
********************************
* Programme:	wpar-management07-wplife-prepare.do
* Date:			4 April 2016
* Author:		Frank Haege, University of Limerick
* Contact:		frank.haege@ul.ie 

* Do-file description
*********************
* This do-file prepares the working party lifecycle dataset for a merge with
* the working party meeting dataset. It generates a general policy area variable as well as
* working party birth and death date variables. For working parties that are succeeded
* by one or two other working parties, it adds the corresponding birth, death, and cause of death
* variables for these successor working parties to the observation. Amongst other things,
* having the successor information in the same row allows for correcting the working party
* ID by linking meeting dates with the time periods in which one party rather than another was
* in existence. These consistency checks and corrections are performed in the next do-file.


* Clear memory  
**************
version 12.0
clear all
macro drop _all
set more off


*************************************************************************
* Prepare data with indicator variable for lacking observations for merge
*************************************************************************

* Load dataset
insheet using "Data management\wpar-management05-workingparties02-code-nomeets.csv", comma names case clear
des, s
* No meetings for 64 groups and sub-groups

* Generate meeting indicator variable
rename meets wpid
generate meets = 0
label var meets "Meetings (yes/no)"
label def yesno 1 "Yes" 0 "No"
label val meets yesno
tab meets, m

* Save dataset
sort wpid
compress
save "Data management\wpar-management07-wplife-prepare-nomeets.dta", replace


* Load dataset
use "Data management\worg-management01.dta", clear
des, s
* 485 working parties

* Check variables
codebook


* Merge with variable indicating lack of meetings
merge 1:1 wpid using "Data management\wpar-management07-wplife-prepare-nomeets.dta"
replace meets = 1 if meets == .
table _merge meets, m
drop _merge


* Generate and save dataset of successor working parties
preserve
	* Keep only observations with a successor WP
	tab succid
	keep if succid != ""
	* Keep only variables relevant for consistency check
	keep death succid succname
	* Rename successor ID variable for merge
	rename succid wpid

	* Drop duplicates
	duplicates list
	duplicates drop

	* Check for coding inconsistencies between successor id and name
	duplicates list wpid
	duplicates list succname

	* Rename remaining variables
	rename death deaths
	rename succname succnames

	* Save successor information dataset
	des, short
	* 65 WPs have a successor WP
	sort wpid
	save "Data management\wpar-management07-wplife-prepare01a-check.dta", replace
restore

* Merge data with successor working party information
merge 1:1 wpid using "Data management\wpar-management07-wplife-prepare01a-check.dta"
* All 65 successor working parties could be matched

* Check that working party name coding is the same
list wpid wpname succnames if wpname != succnames & succnames != ""
list wpid wpname birth deaths if birth != deaths & succnames != "" & birth != .
* These are all sub-groups that had been temporarily abolished


*********************************************
* Generation of successor indicator variables
*********************************************

* Generate indicator variable for successor working parties
generate succwp = 0
replace succwp = 1 if _merge == 3
label var succwp "WP successor (yes/no)
label val succwp yesno
tab succwp, m

* Drop merged variables
drop deaths succnames _merge

* Generate a dataset of successor WPs that have a successor WP themselves
tab succwp if succid != "", m
tab succwp if succid == "", m
* Out of 89 observations with a successor WP, 10 are a successor WP themselves

preserve
	* Keep those observations that have been identified 
	* as successor WPs and have a successor WP themselves
	keep if succwp == 1 & succid != ""
	* Keep only relevant variables
	keep succid succname
	* Rename variable for merge
	rename succid wpid

	* Drop duplicates
	duplicates list
	duplicates drop

	* Save successor information dataset
	des, short
	* 8 unique successor of successor WPs
	list wpid succname
	drop succname
	* 8 working parties are second successor working parties
	sort wpid
	save "Data management\wpar-management07-wplife-prepare01b-secsucc.dta", replace
restore

* Merge data with second successor working party information
merge 1:1 wpid using "Data management\wpar-management07-wplife-prepare01b-secsucc.dta"
* All 8 second successor working parties could be matched

* Generate indicator variable for successor working parties
generate succwp2 = 0
replace succwp2 = 1 if _merge == 3
label var succwp2 "WP successor 2 (yes/no)
label val succwp2 yesno
tab succwp, m
tab succwp succwp2, m
drop _merge


************************************************************
* Add successor WP information to predecessor WP observation
************************************************************

* Generate successor working party dataset for merge
tab succwp, m
preserve
	* Keep only successor WP observations
	keep if succwp == 1
	des, short
	* Keep only relevant variables
	keep wpid wpname birth death birthdate deathdate lcensor cod succid succname policy sectornew sectorold
	* Rename successor information to make sure it is kept after merge
	rename succid s1succid 
	label var s1succid "S1 successor ID"
	rename succname s1succname
	label var s1succname "S1 successor name"
	* Rename variable for merge
	rename wpid succid
	* Rename other variables
	rename wpname s1name
	label var s1name "S1 name"
	rename birth s1birth
	label var s1birth "S1 establishment date"
	rename death s1death
	label var s1death "S1 abolishment date"
	rename birthdate s1birthdate
	label var s1birthdate "S1 exact establishment date (yes/no)"
	rename deathdate s1deathdate
	label var s1deathdate "S1 exact abolishment date (yes/no)"
	rename lcensor s1lcensor
	label var s1lcensor "S1 left censored (yes/no)"
	rename cod s1cod
	label var s1cod "S1 abolishment reason"
	rename policy s1policy
	label var s1policy "S1 policy area"
	rename sectornew s1sectornew
	label var s1sectornew "S1 sector (post-Seville)"
	rename sectorold s1sectorold
	label var s1sectorold "S1 sector (pre-Seville"
	order s1sectornew, after(s1sectorold)
	* Save dataset
	order succid s1birth* s1death* s1name s1*
	des, s
	* 65 successor groups
save "Data management\wpar-management07-wplife-prepare01c-successors.dta", replace
restore

* Merge data with successor working party birth and death dates
tab succid, m
merge m:1 succid using "Data management\wpar-management07-wplife-prepare01c-successors.dta"
* 89 working parties are succeeded by one of the 65 successor working parties
list succname s1name if succname != s1name
drop succname
rename succid s1id
label var s1id "S1 ID"
order s1id, before(s1birth)
drop _merge

* Generate earliest and latest date for birth of successor working party if date is uncertain
* Usually, the exact birth date is not known, only the document date of the list of preparatory bodies
* in which the WP was first mentioned. At this point in time, the WP was most definitely established,
* but it was probably established sometime between the drafting of the current and the previous list
tab s1birthdate, m
sort s1birth
list s1birth s1id s1name if s1birthdate == 0

* Generate variable for minimum date (i.e. document date of previous Council list of preparatory bodies)
generate s1birthmin = .
format %td s1birthmin 

* If birth date is certain, use that date as both minimum and maximum
replace s1birthmin = s1birth if s1birthdate == 1

* If birth date is not certain, use the date of the previous Council document as minimum
* Only the dates of original documents, not corrections and revisions, are taken as possible earlier dates
replace s1birthmin = d(05jul2000) if s1birth == d(08feb2001) & s1birthdate == 0
replace s1birthmin = d(10jul2001) if s1birth == d(30oct2001) & s1birthdate == 0
replace s1birthmin = d(16nov2001) if s1birth == d(15jul2002) & s1birthdate == 0
replace s1birthmin = d(15jul2002) if s1birth == d(04mar2003) & s1birthdate == 0
replace s1birthmin = d(04mar2003) if s1birth == d(10jul2003) & s1birthdate == 0
replace s1birthmin = d(25aug2004) if s1birth == d(27jun2005) & s1birthdate == 0
replace s1birthmin = d(05dec2005) if s1birth == d(24apr2006) & s1birthdate == 0
replace s1birthmin = d(13jul2007) if s1birth == d(21jan2008) & s1birthdate == 0
replace s1birthmin = d(21jan2008) if s1birth == d(08jul2008) & s1birthdate == 0
replace s1birthmin = d(17jul2008) if s1birth == d(16jan2009) & s1birthdate == 0
replace s1birthmin = d(30jun2009) if s1birth == d(29jan2010) & s1birthdate == 0
replace s1birthmin = d(29jan2010) if s1birth == d(20jul2010) & s1birthdate == 0
replace s1birthmin = d(20jul2010) if s1birth == d(01feb2011) & s1birthdate == 0
replace s1birthmin = d(24jan2011) if s1birth == d(22jun2011) & s1birthdate == 0
list s1birthmin s1birth s1id s1name if s1birthdate == 0
label var s1birthmin "S1 establishment date (min)"
rename s1birth s1birthmax
label var s1birthmax "S1 establishment date (max)"
order s1birthmin, before(s1birthmax)

* Generate successor working party dataset for merge
tab succwp2, m
preserve
	* Keep only successor WP observations
	keep if succwp2 == 1
	des, short
	* Keep only relevant variables
	keep wpid wpname birth death birthdate deathdate lcensor cod s1id s1name policy sectornew sectorold
	* Rename successor information to make sure it is kept after merge
	rename s1id s2succid 
	label var s2succid "S2 successor ID"
	rename s1name s2succname
	label var s2succname "S2 successor name"
	* Rename variable for merge
	rename wpid s1succid
	* Rename other variables
	rename wpname s2name
	label var s2name "S2 name"
	rename birth s2birth
	label var s2birth "S2 establishment date"
	rename death s2death
	label var s2death "S2 abolishment date"
	rename birthdate s2birthdate
	label var s2birthdate "S2 exact establishment date (yes/no)"
	rename deathdate s2deathdate
	label var s2deathdate "S2 exact abolishment date (yes/no)"
	rename lcensor s2lcensor
	label var s2lcensor "S2 left censored (yes/no)"
	rename cod s2cod
	label var s2cod "S2 abolishment reason"
	rename policy s2policy
	label var s2policy "S2 policy area"
	rename sectornew s2sectornew
	label var s2sectornew "S2 sector (post-Seville)"
	rename sectorold s2sectorold
	label var s2sectorold "S2 sector (pre-Seville"
	order s2sectornew, after(s2sectorold)
	* Save dataset
	order s1succid s2birth* s2death* s2*
	des, s
	* 8 successor of successor groups
save "Data management\wpar-management07-wplife-prepare01d-successors02.dta", replace
restore

* Merge data with successor working party birth and death dates
tab s1succid, m
merge m:1 s1succid using "Data management\wpar-management07-wplife-prepare01d-successors02.dta"
* 12 successor working parties are succeeded by one of the 8 second successor working parties
list if s1succname != s2name
drop s1succname
list wpid wpname s1name s2name if _merge == 3
list if s2succname != ""
* No further successor working parties
drop s2succname s2succid
rename s1succid s2id
label var s2id "S2 ID"
order s2id, before(s2birth)
tab succwp2 _merge, m
drop _merge

* Generate earliest and latest date for birth of second successor working party if date is uncertain.
tab s2birthdate, m
sort s2birth
list s2birth s1id s2name if s2birthdate == 0

* Generate variable for minimum date (i.e. document date of previous Council list of preparatory bodies)
generate s2birthmin = .
format %td s2birthmin 

* If birth date is certain, use that date as both minimum and maximum
replace s2birthmin = s2birth if s2birthdate == 1

* If birth date is not certain, use the date of the previous Council document as minimum
* Only the dates of original documents, not corrections and revisions, are taken as possible earlier dates
replace s2birthmin = d(05jul2000) if s2birth == d(08feb2001) & s2birthdate == 0
replace s2birthmin = d(10jul2001) if s2birth == d(30oct2001) & s2birthdate == 0
replace s2birthmin = d(16nov2001) if s2birth == d(15jul2002) & s2birthdate == 0
replace s2birthmin = d(15jul2002) if s2birth == d(04mar2003) & s2birthdate == 0
replace s2birthmin = d(04mar2003) if s2birth == d(10jul2003) & s2birthdate == 0
replace s2birthmin = d(25aug2004) if s2birth == d(27jun2005) & s2birthdate == 0
replace s2birthmin = d(05dec2005) if s2birth == d(24apr2006) & s2birthdate == 0
replace s2birthmin = d(11jan2008) if s2birth == d(08jul2008) & s2birthdate == 0
replace s2birthmin = d(17jul2008) if s2birth == d(16jan2009) & s2birthdate == 0
replace s2birthmin = d(30jun2009) if s2birth == d(29jan2010) & s2birthdate == 0
replace s2birthmin = d(29jan2010) if s2birth == d(20jul2010) & s2birthdate == 0
list s2birthmin s2birth s2id s2name if s2birthdate == 0
label var s2birthmin "S2 establishment date (min)"
rename s2birth s2birthmax
label var s2birthmax "S2 establishment date (max)"
order s2birthmin, before(s2birthmax)


* Sort and save dataset
sort wpid
order wprunno wpid meets birthdate birth deathdate death s1id s1birthdate s1birthmin /*
	*/ s1birthmax s1deathdate s1death s2id s2birthdate s2birthmin s2birthmax s2deathdate s2death
order succwp succwp2, after(cod)
compress
des
save "Data management\wpar-management07-wplife-prepare02.dta", replace

* Exit
log close
exit
